<?php
/**
 * Created by PhpStorm.
 * User: hp
 * Date: 2017/11/23
 * Time: 19:36
 */

namespace App\Modules\SendOrder\Models;

use DB;
use Illuminate\Database\Eloquent\Model;

class SendOrderStat extends Model
{
    protected $table = 'send_orders_stats';

    protected $fillable = ['send_order_id', 'book_name', 'bid', 'recharge_amount', 'uv', 'pv', 'paid_num', 'unpaid_num', 'distribution_channel_id', 'recharge_amount_in_half_day',
        'recharge_amount_in_two_weeks', 'send_time', 'cost', 'year_recharge_user_num', 'ticket_recharge_user_num', 'first_recharge_amount', 'first_recharge_user_num',
        'year_paid_num', 'year_unpaid_num', 'pay_user_num', 'force_user_num', 'register_user_num', 'distribution_channel_name', 'name', 'charge_type', 'create_time', 'recharge_amount_in_one_day', 'recharge_amount_in_three_days'];

    //获取派单obj
    static function search($params = [])
    {
        $order_field = 'distribution_channel_id';
        $order_type = isset($params['order_type']) ? $params['order_type'] : 'asc';

        if (isset($params['order_field']) && $params['order_field']) {
            switch ($params['order_field']) {
                case 1:
                    $order_field = 'recharge_amount_in_half_day';
                    break;
                case 2:
                    $order_field = 'recharge_amount_in_two_weeks';
                    break;
                case 3:
                    $order_field = 'recharge_amount';
                    break;
                case 4:
                    $order_field = 'first_recharge_amount';
                    break;
            }
        }

        $search_obj = self::orderBy($order_field, $order_type);
        if (isset($params['start_time']) && $params['start_time']) $search_obj->where('send_time', '>=', $params['start_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('send_time', '<=', $params['end_time']);
        if (isset($params['send_order_id']) && $params['send_order_id']) $search_obj->where('send_order_id', '=', $params['send_order_id']);
        if (isset($params['bid']) && $params['bid']) $search_obj->where('bid', '=', $params['bid']);
        if (isset($params['name']) && $params['name']) $search_obj->where('name', 'like', "%" . $params['name'] . "%");
        if (isset($params['book_name']) && $params['book_name']) $search_obj->where('book_name', '=', $params['book_name']);
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) $search_obj->where('distribution_channel_id', '=', $params['distribution_channel_id']);
        if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) $search_obj->whereIn('distribution_channel_id', $params['distribution_channel_ids']);
        if (isset($params['order_status']) && $params['order_status']) {
            if ($params['order_status'] == 1) $search_obj->where('force_user_num', '<', 20);
            if ($params['order_status'] == 2) $search_obj->where('force_user_num', '>=', 20);
        }

        $search_obj->select(
            DB::raw('sum(cost) cost'),
            DB::raw('sum(recharge_amount_in_three_days) chargeAmountIn72hours'),
            DB::raw('sum(pv) clickNum'),
            DB::raw('sum(pay_user_num) pay_user_num'),
            DB::raw('sum(force_user_num) fansNum'),
            DB::raw('sum(first_recharge_user_num) first_recharge_user_num'),
            DB::raw('sum(register_user_num) register_num'));
        \Log::info($search_obj->toSql());
        return $search_obj->get();
    }

    static function getInfos($params = [], $is_all = false)
    {
        $search_obj = self::orderBy('send_time', 'desc');
        $search_obj->where('bid', '>', 0);
        if (isset($params['start_time']) && $params['start_time']) $search_obj->where('send_time', '>=', $params['start_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('send_time', '<=', $params['end_time'] . ' 23:59:59');
        if (isset($params['book_name']) && $params['book_name']) $search_obj->where('book_name', 'like', '%' . $params['book_name'] . '%');
        if (isset($params['channels']) && $params['channels']) $search_obj->whereIn('distribution_channel_id', $params['channels']);

        $search_obj->select(
            'id',
            'send_time',
            DB::raw('date(send_time) date'),
            DB::raw('sum(recharge_amount) charge_amount'),
            DB::raw('sum(paid_num) success_order_num'),
            DB::raw('sum(pay_user_num) pay_user_num'),
            DB::raw('sum(unpaid_num) unpaid_num'),
            'book_name',
            'bid',
            DB::raw('count(id) send_order_number'));

        $search_obj->groupBy('book_name')->groupBy(DB::raw('date(send_time)'));

        if ($is_all) {
            return $search_obj->get();
        } else {
            return $search_obj->paginate();
        }
    }

    static function getInfosByType($params = [], $is_all = false)
    {
        $search_obj = self::join('distribution_channels', 'send_orders_stats.distribution_channel_id', '=', 'distribution_channels.id')
            ->leftjoin('channel_users', 'channel_users.id', '=', 'distribution_channels.channel_user_id');
        if (isset($params['start_time']) && $params['start_time']) $search_obj->where('send_orders_stats.send_time', '>=', $params['start_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('send_orders_stats.send_time', '<=', $params['end_time'] . ' 23:59:59');
        if (isset($params['book_name']) && $params['book_name']) $search_obj->where('send_orders_stats.book_name', 'like', '%' . $params['book_name'] . '%');
        if (isset($params['channels']) && $params['channels']) $search_obj->whereIn('send_orders_stats.distribution_channel_id', $params['channels']);

        $search_obj->where('bid', '>', 0);
        $search_obj->select(
            'send_orders_stats.id',
            'channel_users.phone',
            'send_time',
            DB::raw('date(send_time) date'),
            'send_orders_stats.distribution_channel_id',
            DB::raw('sum(recharge_amount) charge_amount'),
            DB::raw('sum(paid_num) success_order_num'),
            DB::raw('sum(pay_user_num) pay_user_num'),
            DB::raw('sum(unpaid_num) unpaid_num'),
            'book_name',
            'bid',
            DB::raw('count(send_orders_stats.id) send_order_number'));

        $search_obj->groupBy('book_name')->groupBy(DB::raw('date(send_time)'));

        if (isset($params['groupBy']) && $params['groupBy']) {
            if ($params['groupBy'] == 'site') {
                $search_obj->groupBy('send_orders_stats.distribution_channel_id');
            } elseif ($params['groupBy'] == 'account') {
                $search_obj->groupBy('channel_users.id');
            }
        }

        $search_obj->orderBy('send_time', 'desc');
        if ($is_all) {
            return $search_obj->get();
        } else {
            return $search_obj->paginate();
        }
    }
}